﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
namespace DAL
{
   public class RoleDAL
    {
        public List<RoleInfo> GetRole(string roleName, int begin, int end, out int total) {
            string sql = "";
            string strWhere = " where 1=1";
            string abc = string.Format(" where 1=1 and RoleName like '%{0}%'", roleName);
            if (roleName == string.Empty)
            {
                sql = string.Format(@"select * from (select ROW_NUMBER() over (order by RoleID desc)
            rowindex,* from RoleInfo {2}) a where rowindex between {0} and {1}", begin, end, strWhere);
            }
            else
            {
                sql = string.Format(@"select * from (select ROW_NUMBER() over (order by RoleID)
            rowindex,* from RoleInfo) a {0}", abc);
            }
            DataTable dt = DBHelper.GetTable(sql);
            List<RoleInfo> list = new List<RoleInfo>();
            if (dt.Rows.Count>0) {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    RoleInfo info = new RoleInfo();
                    info.RoleID =Convert.ToInt32(dt.Rows[i]["RoleID"]);
                    info.RoleName = dt.Rows[i]["RoleName"].ToString();
                    info.RoleReMark = dt.Rows[i]["RoleReMark"].ToString();
                    list.Add(info);
                }
            }
            string sqltotal = " select COUNT(*) from RoleInfo" + strWhere;
            DataTable totalDt = DBHelper.GetTable(sqltotal);
            total = Convert.ToInt32(totalDt.Rows[0][0]);
            return list;
        }

        public List<RoleInfo> BindRole(string role)
        {
            string sql = string.Format("select RoleID, RoleName from RoleInfo ");
            DataTable dt = DBHelper.GetTable(sql);
            List<RoleInfo> list = new List<RoleInfo>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    RoleInfo info = new RoleInfo();
                    info.RoleID = Convert.ToInt32(dt.Rows[i]["RoleID"]);
                    info.RoleName = dt.Rows[i]["RoleName"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }

        public string Delete(int rid)
        {
            string sql = string.Format("  delete from RoleInfo where RoleID={0}",rid);
            int i = DBHelper.ExecutNonQuery(sql);
            return i > 0 ? "OK" : "NO";
        }

        public string Add(RoleInfo role)
        {
            string sql = string.Format(" insert into RoleInfo(RoleName,RoleReMark) values('{0}','{1}')", role.RoleName, role.RoleReMark);
            int i = DBHelper.ExecutNonQuery(sql);
            return i > 0 ? "OK" : "NO";
        }

        public string Update(RoleInfo role)
        {
            string sql = string.Format("update RoleInfo set RoleName='{0}',RoleReMark='{1}' where RoleID={2}",role.RoleName,role.RoleReMark,role.RoleID);
            int i= DBHelper.ExecutNonQuery(sql);
            return i > 0 ? "OK" : "NO";
        }
    }
}
